﻿Module modStatistics
    Dim Sql As String
    Dim dsCommon As DataSet
    Dim daCommon As OleDb.OleDbDataAdapter

    '## 월별 매입 통계 전체 만들기
    Public Sub gSubLookupOutAll()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stAmount As String = ""
        Dim stat As String = ""
        Dim isValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(planDt,4), right(left(planDt,7),2),  sum(amount) FROM tblPayment WHERE flag='Y' GROUP BY left(planDt,4), right(left(planDt,7),2) ORDER BY  left(planDt,4), right(left(planDt,7),2)"

        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stAmount = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)


                If stYear <> oStYear Then
                    isValue = gAFieldValue("SELECT count(*) FROM tblStatistics WHERE yr='" + stYear + "' AND inOut='OT' AND typeCd=9999 AND types='all'")
                    If isValue = 0 Then
                        Call gSubSaveAction("INSERT INTO tblStatistics(Yr, inOut, typeCd, types) VALUES('" + stYear + "','OT',9999,'all')", "")
                    End If
                End If
                Call gSubSaveAction("UPDATE tblStatistics SET m" + stMonth + "='" + stAmount + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='OT' AND typeCd=9999 AND types='all'", "")

                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 월별 매입 통계 계정별 만들기
    Public Sub gSubLookupOutAcc()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stAmount As String = ""
        Dim stat As String = ""
        Dim stAcc As String = ""
        Dim oStAcc As String = ""
        Dim isValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(planDt,4), right(left(planDt,7),2),  sum(amount),  IIF(ISNULL(accountcd), '', accountcd) FROM tblPayment WHERE flag='Y' GROUP BY left(planDt,4), right(left(planDt,7),2), accountCd ORDER BY left(planDt,4), right(left(planDt,7),2), accountCd"

        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            '## 통계 데이터 초기화
            Call gSubSaveAction("UPDATE tblStatistics SET issueDt='" + curDt + "',flag='N' WHERE inOut='OT' AND types='acc'", "")

            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stAmount = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)
                stAcc = dsCommon.Tables("StatisticsAll").Rows(c).Item(3)
                If stAcc = "" Then
                    stAcc = 0
                End If

                isValue = gAFieldValue("SELECT count(*) FROM tblStatistics WHERE yr='" + stYear + "' AND inOut='OT' AND typeCd=" + stAcc + " AND types='acc'")
                If isValue = 0 Then
                    Call gSubSaveAction("INSERT INTO tblStatistics(Yr, inOut, typeCd, types) VALUES('" + stYear + "','OT'," + stAcc + ",'acc')", "")
                End If
                Call gSubSaveAction("UPDATE tblStatistics SET m" + stMonth + "='" + stAmount + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='OT' AND typeCd=" + stAcc + " AND types='acc'", "")

                oStAcc = stAcc
                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 월별 매출 통계 전체 만들기
    Public Sub gSubLookupInAll()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stAmount As String = ""
        Dim stat As String = ""
        Dim isValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(incomeDt,4), right(left(incomeDt,7),2),  sum(amount) FROM tblIncome WHERE flag='Y' GROUP BY left(incomeDt,4), right(left(incomeDt,7),2) ORDER BY  left(incomeDt,4), right(left(incomeDt,7),2)"

        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            '##통계 데이터 초기화
            Call gSubSaveAction("UPDATE tblStatistics SET issueDt='" + curDt + "',flag='N' WHERE inOut='IN' AND typeCd=9999 AND types='all'", "")
            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stAmount = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)


                If stYear <> oStYear Then
                    isValue = gAFieldValue("SELECT count(*) FROM tblStatistics WHERE yr='" + stYear + "' AND inOut='IN' AND typeCd=9999 AND types='all'")
                    If isValue = 0 Then
                        Call gSubSaveAction("INSERT INTO tblStatistics(Yr, inOut, typeCd, types) VALUES('" + stYear + "','IN',9999,'all')", "")
                    End If
                End If
                Call gSubSaveAction("UPDATE tblStatistics SET m" + stMonth + "='" + stAmount + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='IN' AND typeCd=9999 AND types='all'", "")

                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 월별 매출 통계 계정별 만들기
    Public Sub gSubLookupInAcc()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stAmount As String = ""
        Dim stat As String = ""
        Dim stAcc As String = ""
        Dim oStAcc As String = ""
        Dim isValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(incomeDt,4), right(left(incomeDt,7),2),  sum(amount),  IIF(ISNULL(accountcd), '', accountcd) FROM tblIncome WHERE flag='Y' GROUP BY left(incomeDt,4), right(left(incomeDt,7),2), accountCd ORDER BY left(incomeDt,4), right(left(incomeDt,7),2), accountCd"

        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            '##통계 데이터 초기화
            Call gSubSaveAction("UPDATE tblStatistics SET issueDt='" + curDt + "',flag='N' WHERE inOut='IN' AND types='acc'", "")

            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stAmount = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)
                stAcc = dsCommon.Tables("StatisticsAll").Rows(c).Item(3)
                If stAcc = "" Then
                    stAcc = 0
                End If

                isValue = gAFieldValue("SELECT count(*) FROM tblStatistics WHERE yr='" + stYear + "' AND inOut='IN' AND typeCd=" + stAcc + " AND types='acc'")
                If isValue = 0 Then
                    Call gSubSaveAction("INSERT INTO tblStatistics(Yr, inOut, typeCd, types) VALUES('" + stYear + "','IN'," + stAcc + ",'acc')", "")
                End If
                Call gSubSaveAction("UPDATE tblStatistics SET m" + stMonth + "='" + stAmount + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='IN' AND typeCd=" + stAcc + " AND types='acc'", "")

                oStAcc = stAcc
                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 월별 매입 통계 회사별별 만들기
    Public Sub gSubLookupOutComp()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stAmount As String = ""
        Dim stat As String = ""
        Dim stComp As String = ""
        Dim oStComp As String = ""
        Dim isValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(planDt,4), right(left(planDt,7),2),  sum(amount),  IIF(ISNULL(compCd), '', compCd) FROM tblPayment WHERE flag='Y' GROUP BY left(planDt,4), right(left(planDt,7),2), compCd ORDER BY left(planDt,4), right(left(planDt,7),2), compCd"

        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            '##통계 데이터 초기화
            Call gSubSaveAction("UPDATE tblStatistics SET issueDt='" + curDt + "',flag='N' WHERE inOut='OT' AND types='comp'", "")

            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stAmount = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)
                stComp = dsCommon.Tables("StatisticsAll").Rows(c).Item(3)
                If stComp = "" Then
                    stComp = 0
                End If

                isValue = gAFieldValue("SELECT count(*) FROM tblStatistics WHERE yr='" + stYear + "' AND inOut='OT' AND typeCd=" + stComp + " AND types='comp'")
                If isValue = 0 Then
                    Call gSubSaveAction("INSERT INTO tblStatistics(Yr, inOut, typeCd, types) VALUES('" + stYear + "','OT'," + stComp + ",'comp')", "")
                End If
                Call gSubSaveAction("UPDATE tblStatistics SET m" + stMonth + "='" + stAmount + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='OT' AND typeCd=" + stComp + " AND types='comp'", "")

                oStComp = stComp
                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub


    '## 월별 매출 통계 계정별 만들기
    Public Sub gSubLookupInComp()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stAmount As String = ""
        Dim stat As String = ""
        Dim stComp As String = ""
        Dim oStComp As String = ""
        Dim isValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(incomeDt,4), right(left(incomeDt,7),2),  sum(amount),  IIF(ISNULL(compCd), '', compCd) FROM tblIncome WHERE flag='Y' GROUP BY left(incomeDt,4), right(left(incomeDt,7),2), compCd ORDER BY left(incomeDt,4), right(left(incomeDt,7),2), compCd"

        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            '##통계 데이터 초기화
            Call gSubSaveAction("UPDATE tblStatistics SET issueDt='" + curDt + "',flag='N' WHERE inOut='IN' AND types='comp'", "")

            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stAmount = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)
                stComp = dsCommon.Tables("StatisticsAll").Rows(c).Item(3)
                If stComp = "" Then
                    stComp = 0
                End If

                isValue = gAFieldValue("SELECT count(*) FROM tblStatistics WHERE yr='" + stYear + "' AND inOut='IN' AND typeCd=" + stComp + " AND types='comp'")
                If isValue = 0 Then
                    Call gSubSaveAction("INSERT INTO tblStatistics(Yr, inOut, typeCd, types) VALUES('" + stYear + "','IN'," + stComp + ",'comp')", "")
                End If
                Call gSubSaveAction("UPDATE tblStatistics SET m" + stMonth + "='" + stAmount + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='IN' AND typeCd=" + stComp + " AND types='comp'", "")

                oStComp = stComp
                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 월별 상품 입출고 통계 만들기
    Public Sub gSubLookupProdQty()

        Call dbConnection()
        Dim addSql As String = ""
        Dim stYear As String = ""
        Dim oStYear As String = ""
        Dim stMonth As String = ""
        Dim stInput As String = ""
        Dim stOutput As String = ""
        Dim stat As String = ""
        Dim stPrdCd As String = ""
        Dim oStPrdCd As String = ""
        Dim isInValue As String = ""
        Dim isOutValue As String = ""
        Dim curDt As DateTime = Now

        dsCommon = New DataSet

        Sql = "SELECT left(issueDt,4), right(left(issueDt,7),2),  sum([output]), sum([input]), IIF(ISNULL(prdCd), '', prdCd) FROM tblPrdInOut WHERE flag='Y' GROUP BY left(issueDt,4), right(left(issueDt,7),2), prdCd ORDER BY left(issueDt,4), right(left(issueDt,7),2), prdCd"
        'MsgBox(Sql)
        Try
            daCommon = New OleDb.OleDbDataAdapter(Sql, dbConn)
            daCommon.Fill(dsCommon, "StatisticsAll")

            '##통계 데이터 초기화
            Call gSubSaveAction("UPDATE tblStatProduct SET issueDt='" + curDt + "',flag='N' WHERE types='QTY'", "")

            For c = 0 To dsCommon.Tables("StatisticsAll").Rows.Count - 1

                stYear = dsCommon.Tables("StatisticsAll").Rows(c).Item(0)
                stMonth = dsCommon.Tables("StatisticsAll").Rows(c).Item(1)
                stOutput = dsCommon.Tables("StatisticsAll").Rows(c).Item(2)
                stInput = dsCommon.Tables("StatisticsAll").Rows(c).Item(3)
                stPrdCd = dsCommon.Tables("StatisticsAll").Rows(c).Item(4)
                If stPrdCd = "" Then
                    stPrdCd = 0
                End If

                isInValue = gAFieldValue("SELECT count(*) FROM tblStatProduct WHERE yr='" + stYear + "' AND inOut='IN' AND prdCd=" + stPrdCd + " AND types='QTY'")
                If isInValue = 0 Then
                    Call gSubSaveAction("INSERT INTO tblStatProduct(Yr, inOut, prdCd, types) VALUES('" + stYear + "','IN'," + stPrdCd + ",'QTY')", "")
                End If
                Call gSubSaveAction("UPDATE tblStatProduct SET m" + stMonth + "='" + stInput + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='IN' AND prdCd=" + stPrdCd + " AND types='QTY'", "")

                isInValue = gAFieldValue("SELECT count(*) FROM tblStatProduct WHERE yr='" + stYear + "' AND inOut='OT' AND prdCd=" + stPrdCd + " AND types='QTY'")
                If isInValue = 0 Then
                    Call gSubSaveAction("INSERT INTO tblStatProduct(Yr, inOut, prdCd, types) VALUES('" + stYear + "','OT'," + stPrdCd + ",'QTY')", "")
                End If
                Call gSubSaveAction("UPDATE tblStatProduct SET m" + stMonth + "='" + stOutput + "', issueDt='" + curDt + "', flag='Y' WHERE yr='" + stYear + "' AND inOut='OT' AND prdCd=" + stPrdCd + " AND types='QTY'", "")

                oStPrdCd = stPrdCd
                oStYear = stYear
            Next

            dbConn.Close()
        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(Sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

End Module
